/*=====================================================================

  File:      WireframeDbStorage.cs
  Summary:   Provides I/O services for wireframes from/to databases
  Date:	     May 27, 2006

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */

using System;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Microsoft.Samples.SqlServer.DB3D
{
	// WireframeFileStorage provides I/O services for wireframes from/to
	// databases.
	//
	// This is a stateless class that could act as a facade in 3-tier
	// scenarios, Wireframe being the data-transfer object
	public static class WireframeDBStorage
	{
        // retrieve the list wireframes available in the server/database
        // pointed by connectionString
        public static Pair<string,int>[] ListWireframes(string connectionString) {
            List<Pair<string,int>> list = new List<Pair<string,int>>();

            using(SqlConnection conn = new SqlConnection(connectionString)) {
                conn.Open();

                SqlCommand cmd = new SqlCommand("ListWireframes", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read()) {
                    list.Add(new Pair<string, int>(reader.GetString(1), reader.GetInt32(0)));
                }
            }

            return list.ToArray();
        }

        // retrieve the list of wireframes along with additional information,
        // specifically the number of triangles per wireframe and its surface
        // Note that both are aggregates, but one (number of triangles) is a
        // standard one (COUNT), and the other is a custom one (Surface). Instead
        // of using a custom type as the return value, simply return a DataTable
        // which is handy for both manipulating and displaying the data
        public static DataTable ListWireFramesDetailed(string connectionString) {
            // Setup an adapter to point to the 'ListWireframesDetailed' stored-proc
            SqlDataAdapter da = new SqlDataAdapter("ListWireframesDetailed", connectionString);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            // Create a new DataTable and fill it with the results of the stored-proc
            DataTable results = new DataTable("Wireframes");
            results.Locale = System.Globalization.CultureInfo.InvariantCulture;
            da.Fill(results);

            return results;
        }

        // remove the wireframe indicated by "id" (and associated triangles) 
        // from the database
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:DisposeObjectsBeforeLosingScope", 
            Justification="SqlCommand objects need not be disposed.")]
        public static void Delete(string connectionString, int id) {
            using(SqlConnection conn = new SqlConnection(connectionString)) {
                conn.Open();

                SqlCommand cmd = new SqlCommand("DeleteWireframe", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@WireframeID", SqlDbType.Int).Value = id;
                cmd.ExecuteNonQuery();
            }
        }

        // create a new wireframe in the database and return the ID of the
        // new wireframe row
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:DisposeObjectsBeforeLosingScope", 
            Justification="SqlCommand objects do not need to be disposed.")]
        public static int Create(Wireframe wireframe, string connectionString, bool doBCP, WireframeProgress progress) {
            if(null == wireframe) throw new ArgumentNullException("wireframe");

            // establish a connection with the database
            using(SqlConnection conn = new SqlConnection(connectionString)) {
                conn.Open();

                // make sure we upload the whole wireframe or nothing at all
                SqlTransaction tx = conn.BeginTransaction();

                try {
                    // define a command to invoke a stored-proc to create a wireframe row
                    SqlCommand cmdWireframe = new SqlCommand("CreateWireframe", conn, tx);

                    cmdWireframe.CommandType = CommandType.StoredProcedure;
                    cmdWireframe.Parameters.Add("@WireframeName", SqlDbType.VarChar, 255).Value = wireframe.Name;

                    // execute and retrieve the id for the new wireframe
                    int wireframeId = (int)(decimal)cmdWireframe.ExecuteScalar();

                    if(doBCP) {
                        InsertTriangleDataBCP(wireframe, conn, tx, progress, wireframeId);
                    }
                    else {
                        InsertTriangleData(wireframe, conn, tx, progress, wireframeId);
                    }

                    // last call to the callback with total nr of triangles
                    if(null != progress)
                        progress(wireframe.Triangles.Length);

                    // commit the changes to the database
                    tx.Commit();
                    return wireframeId;
                }
                catch(Exception) {
                    // in case something goes wrong, rollback the transaction
                    // and let the exception flow up
                    tx.Rollback();
                    throw;
                }
            }
        }

        // Insert triangle data into the Triangle table in the database by
        // executing an INSERT statement for each triangle. Note that while
        // this method doesn't require intermediate tables, it does a large
        // number of network round-trips.
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:DisposeObjectsBeforeLosingScope",
            Justification = "SqlCommand objects need not be disposed.")]
        static void InsertTriangleData(Wireframe wireframe, SqlConnection conn, SqlTransaction tx, 
                                       WireframeProgress progress, int wireframeId) {

            // insert the triangles one at a time
            // set the command to execute the stored-proc for inserting triangles
            SqlCommand cmdTriangle = new SqlCommand("CreateTriangle", conn, tx);

            cmdTriangle.CommandType = CommandType.StoredProcedure;
            cmdTriangle.Parameters.Add("@WireframeID", SqlDbType.Int).Value = wireframeId;

            // note that for UDT parameters we also indicate the UDT type name
            // as defined in the database
            SqlParameter triangleParam = cmdTriangle.Parameters.Add("@Triangle", SqlDbType.Udt);
            triangleParam.UdtTypeName = "Triangle3D";

            // scan the Triangles array and send one by one to the database
            for(int i = 0; i < wireframe.Triangles.Length; i++) {
                triangleParam.Value = wireframe.Triangles[i];
                cmdTriangle.ExecuteNonQuery();

                // if the user gave us a delegate, report progress every
                // 100 triangles
                if(null != progress && i % 100 == 0)
                    progress(i);
            }
        }

        // Insert triangle data into the Triangle table in the database by
        // bulk-copying the data into a temporary table and then using a
        // single SQL statement to re-shape it and insert it into the Triangle
        // table. This method does optimal use of the network (1 round-trip), but
        // it requires a temporary table.
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:DisposeObjectsBeforeLosingScope",
            Justification = "SqlCommand objects need not be disposed.")]
        [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", 
                         Justification = "Keeping the 'progress' parameter for symetry with InsertTriangleData",
                         MessageId = "progress")]
        static void InsertTriangleDataBCP(Wireframe wireframe, SqlConnection conn, SqlTransaction tx, 
                                          WireframeProgress progress, int wireframeId) {
            // The BCP upload is done in two steps
            //  1- upload the data into a temporary table, with UDTs as strings
            //  2- use an INSERT ... SELECT to transform the data and push it into the Triangle table
            // This allows us to work-around the limitation of UDTs in BCP

            // create a data-table and copy the triangle data into it
            DataTable t = new DataTable();
            t.Locale = System.Globalization.CultureInfo.InvariantCulture;
            t.Columns.Add("TriangleObj", typeof(string));

            for(int i = 0; i < wireframe.Triangles.Length; i++) {
                t.Rows.Add(wireframe.Triangles[i].ToString());
            }
            
            // create a temporary table to upload the data
            SqlCommand cmd = new SqlCommand("CREATE TABLE #db3d_bcp (TriangleObj VARCHAR(512))", conn, tx);
            cmd.ExecuteNonQuery();

            // move the data to the server using bulk-copy
            using(SqlBulkCopy bcp = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tx)) {
                bcp.DestinationTableName = "#db3d_bcp";
                bcp.WriteToServer(t);
            }

            // convert the string column to UDTs and copy the data to
            // the final table, associating it with the given wireframe identifier
            cmd.CommandText = "INSERT INTO Triangle (WireframeID, TriangleObj) SELECT @id AS WireframeID, CONVERT(Triangle3D, TriangleObj) AS TriangleObj FROM #db3d_bcp";
            cmd.Parameters.AddWithValue("@id", wireframeId);
            cmd.ExecuteNonQuery();
        }

        // load the wireframe indicated by wireframeId from the database
        public static Wireframe Load(string connectionString, int wireframeId, WireframeProgress progress) {

            Wireframe wireframe = new Wireframe();

            // establish a connection with the database
            using(SqlConnection conn = new SqlConnection(connectionString)) {
                conn.Open();

                // define a command to invoke a stored-proc to retrieve the triangles
                // for the wireframe
                SqlCommand cmd = new SqlCommand("GetWireframe", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@WireframeID", SqlDbType.Int).Value = wireframeId;

                // the name of the mesh is returned in an output parameter
                SqlParameter nameParam = cmd.Parameters.Add("@WireframeName", SqlDbType.VarChar, 255);
                nameParam.Direction = ParameterDirection.Output;

                // temporary storage for the triangles (we'll convert it
                // into Triangle[] at the end of the method)
			    List<Triangle> list = new List<Triangle>();
			    int i=0;

                // execute the command and obtain the triangle list back
                using(SqlDataReader reader = cmd.ExecuteReader()) {
                    // if the user gave us a delegate, report progress
                    if(null != progress)
                        progress(0); // "just starting"

                    while(reader.Read()) {
                        // Triangle is a UDT, so we get a real object from the reader
                        Triangle t = (Triangle)reader[1];
                        list.Add(t);

                        // if the user gave us a delegate, report progress every
                        // 100 triangles
                        i++;
                        if(null != progress && i % 100 == 0)
                            progress(i);
                    }
                }

                // last call to the callback with i==total nr of triangles
                if(null != progress)
                    progress(i);

                // get the wireframe name
                wireframe.Name = (string)nameParam.Value;

                // turn the list into an array and attach it to the wireframe
                wireframe.Triangles = list.ToArray();

                return wireframe;
            }
        }
    }
}
